package com.redteam.data.dao;

import com.redteam.data.beans.CharterCustomer;
import com.redteam.data.beans.CustomerPilot;
import com.redteam.data.beans.Pilot;
import com.redteam.data.beans.RentalItem;
import com.redteam.data.beans.Reservation;
import com.redteam.data.beans.Student;
import com.redteam.data.dao.interfaces.DAO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * DAO for retrieving a <code>Reservation</code> data
 * @author Jezreel Wilson
 * @version 1.0
 */
public class ReservationDAO extends AbstractDAO implements DAO {

    private static ReservationDAO dao;
    private static DataSource ds;
    private final String tableName;

    static {
        dao = new ReservationDAO();
    }

    private ReservationDAO() {
        getDataSource();
        tableName = "HIGHFLIERS.Reservation";
    }

    /**
     * Static method for getting <code>ReservationDAO</code>
     * @return
     */
    public static ReservationDAO getInstance() {
        return dao;
    }

    private void getDataSource() {
        try {
            InitialContext ic = new InitialContext();
            ds = (DataSource) ic.lookup("java:comp/env/jdbc/redteam");

        } catch (NamingException ex) {
            Logger.getLogger(ReservationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * (@inherentDoc)
     */
    @Override
    public String buildUpdateQuery(Object obj) {
        Reservation reservation = (Reservation) obj;
        StringBuilder sb = new StringBuilder("UPDATE ");
        sb.append(getTableName()).append(" SET RENTER_ID = ").append(reservation.getReserver().getId());
        sb.append(", RENTAL_ITEM_ID = ").append(reservation.getRentalItem().getId());
        sb.append(", PILOT_ID = ").append(reservation.getPilot().getId());
        sb.append(", START_DATE = '").append(reservation.getStartDate());
        sb.append("', END_DATE = '").append(reservation.getEndDate());
        sb.append("', START_TIME = '").append(reservation.getStartTime());
        sb.append("', END_TIME = '").append(reservation.getEndTime());
        sb.append("', PRICE = ").append(reservation.getPrice());
        sb.append(", RESERVATION_STATE = '").append(reservation.getReservationState());
        sb.append("', RENTALTIME = '").append(reservation.getRentalTime());
        sb.append("', DISCRIMINATOR ='").append(reservation.getDiscriminator());
        sb.append("', WET_DRY='").append(reservation.getAgreement());
        sb.append("' WHERE ID = ").append(reservation.getId());
        return sb.toString();
    }

    /**
     * (@inherentDoc)
     */
    @Override
    public Reservation getObjectOut(ResultSet rs) {
        Reservation reservation = new Reservation();
        try {
            DAO ddao = PersonsDAO.getInstance();
            long renterId = rs.getLong("RENTER_ID");

            PersonsDAO pdao = PersonsDAO.getInstance();

            if (pdao.personDescriminator(renterId).equals("student")) {
                DAO dao2 = StudentDAO.getInstance();
                reservation.setReserver((Student) dao2.getSingle(renterId));
            } else if (pdao.personDescriminator(renterId).equals("charterCustomer")) {
                DAO dao2 = CharterCustomerDAO.getInstance();
                reservation.setReserver((CharterCustomer) dao2.getSingle(renterId));
            } else if (pdao.personDescriminator(renterId).equals("pilot")) {
                DAO dao2 = CustomerPilotDAO.getInstance();
                reservation.setReserver((CustomerPilot) dao2.getSingle(renterId));
            }

            ddao = PilotDAO.getInstance();
            reservation.setPilot((Pilot) ddao.getSingle(rs.getLong("PILOT_ID")));
            ddao = RentalItemDAO.getInstance();
            reservation.setRentalItem((RentalItem) ddao.getSingle(rs.getLong("RENTAL_ITEM_ID")));
            reservation.setId(rs.getLong("ID"));
            reservation.setStartDate(rs.getDate("START_DATE"));
            reservation.setEndDate(rs.getDate("END_DATE"));
            reservation.setStartTime(rs.getTime("START_TIME"));
            reservation.setEndTime(rs.getTime("END_TIME"));
            reservation.setPrice(rs.getDouble("PRICE"));
            reservation.setReservationState(rs.getString("RESERVATION_STATE"));
            reservation.setAgreement(rs.getString("WET_DRY"));
            reservation.setDiscriminator(rs.getString("DISCRIMINATOR"));
            reservation.setRentalTime(rs.getString("RENTALTIME"));
        } catch (SQLException ex) {
            Logger.getLogger(ReservationDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            return reservation;
        }
    }

    /**
     * Gets all <code>Reservations</code> for specific <code>Pilot</code>.
     * @param pilotID
     * Pilot's id
     * @return
     * List of Reservations
     * @throws SQLException
     */
    public List<Reservation> getAllforPilot(long pilotID) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        List<Reservation> reservationList = new ArrayList<Reservation>();
        try {
            conn = this.getDS().getConnection();
            st = conn.createStatement();
            StringBuilder sb = new StringBuilder("SELECT * FROM ").append(this.getTableName()).append(" WHERE PILOT_ID = ");
            sb.append(pilotID);
            rs = st.executeQuery(sb.toString());
            while (rs.next()) {
                reservationList.add(this.getObjectOut(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ReservationDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(rs, st, conn);
            return reservationList;
        }
    }

    /**
     * Gets all <code>Reservations</code> for specific <code>Pilot</code>.
     * @param renterID
     * Renter's id
     * @return
     * List of Reservations
     * @throws SQLException
     */
    public List<Reservation> getAllforRenter(long renterID) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        List<Reservation> reservationList = new ArrayList<Reservation>();
        try {
            conn = this.getDS().getConnection();
            st = conn.createStatement();
            StringBuilder sb = new StringBuilder("SELECT * FROM ").append(this.getTableName()).append(" WHERE RENTER_ID = ");
            sb.append(renterID);
            rs = st.executeQuery(sb.toString());
            while (rs.next()) {
                reservationList.add(this.getObjectOut(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ReservationDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(rs, st, conn);
            return reservationList;
        }
    }

    /**
     * Gets all <code>Reservations</code> for specific <code>rentalItem</code>.
     * @param itemID
     * Item's id
     * @return
     * List of Reservations
     * @throws SQLException
     */
    public List<Reservation> getAllforAnyItem(long itemID) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        List<Reservation> reservationList = new ArrayList<Reservation>();
        try {
            conn = this.getDS().getConnection();
            st = conn.createStatement();
            StringBuilder sb = new StringBuilder("SELECT * FROM ").append(this.getTableName()).append(" WHERE RENTAL_ITEM_ID = ");
            sb.append(itemID);
            rs = st.executeQuery(sb.toString());
            while (rs.next()) {
                reservationList.add(this.getObjectOut(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ReservationDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(rs, st, conn);
            return reservationList;
        }
    }

    /**
     * (@inherentDoc)
     */
    @Override
    public String buildInsertQuery(Object obj) {
        Reservation reservation = (Reservation) obj;
        StringBuilder sb = new StringBuilder("INSERT INTO ").append(this.getTableName());
        sb.append(" (RENTER_ID,RENTAL_ITEM_ID,PILOT_ID,START_DATE,END_DATE,START_TIME,END_TIME,");
        sb.append("PRICE,RESERVATION_STATE,WET_DRY,DISCRIMINATOR)");
        sb.append(" VALUES (").append(reservation.getReserver().getId()).append(",").append(reservation.getRentalItem().getId()).append(",");
        sb.append(reservation.getPilot().getId()).append(",'").append(reservation.getStartDate()).append("','");
        sb.append(reservation.getEndDate()).append("','").append(reservation.getStartTime()).append("','");
        sb.append(reservation.getEndTime()).append("',").append(reservation.getPrice()).append(",'");
        sb.append(reservation.getReservationState()).append("','").append(reservation.getAgreement());
        sb.append("','").append(reservation.getDiscriminator()).append("')");
        return sb.toString();
    }

    /**
     * Gets all <code>Students</code> for a given <code>Instructor</code>
     * @param instructorId
     * Id of <code>Instructor</code>
     * @return
     * List of <code>Students</code>
     */
    public List<Student> getStudentsforInstructor(long instructorId) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        List<Student> listofstudents = new ArrayList<Student>();
        try {
            conn = this.getDS().getConnection();
            st = conn.createStatement();
            StringBuilder sb = new StringBuilder("SELECT * FROM ").append(this.getTableName()).append(" WHERE PILOT_ID = ");
            sb.append(instructorId);
            rs = st.executeQuery(sb.toString());
            DAO sdao = StudentDAO.getInstance();
            while (rs.next()) {
                listofstudents.add((Student) sdao.getSingle(getObjectOut(rs).getReserver().getId()));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ReservationDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnection(rs, st, conn);
            return listofstudents;
        }
    }

    /**
     * (@inherentDoc)
     */
    @Override
    public long getObjectId(Object o) {
        Reservation reservation = (Reservation) o;
        return reservation.getId();
    }

    /**
     * (@inherentDoc)
     */
    @Override
    public DataSource getDS() {
        return ds;
    }

    /**
     * (@inherentDoc)
     */
    @Override
    public String getTableName() {
        return tableName;

    }
}
